/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.util.List;
import java.util.ArrayList;
import Model.UserBean;
import Model.ChoreBean;
import Model.HouseholdBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author wrjohns
 */
public class HouseholdDAO {

    public static Connection DBConn;

    public static void init() throws SQLException {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
        } catch (ClassNotFoundException e) {
            System.err.println(e.getMessage());
            System.exit(0);
        }
        DBConn = DriverManager.getConnection("jdbc:derby://localhost:1527/ChoreBoard");
    }

    public static void terminate() throws SQLException {
        DBConn.close();
    }

    public static List<UserBean> getUsersFromHousehold(String householdName) throws SQLException {
        List<UserBean> usersFromHousehold = new ArrayList<UserBean>();
        DBConn = DriverManager.getConnection("jdbc:derby://localhost:1527/ChoreBoard");
        Statement stmt = DBConn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from PERSON where HOUSEHOLD='" + householdName + "'");
        while (rs.next()) {
            UserBean user = new UserBean();
            user.setUserName(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setHouseName(rs.getString("household"));
            user.setPoints(rs.getInt("points"));
            user.setIsAdmin(rs.getBoolean("houseadmin"));
        }
        return usersFromHousehold;
    }

    public static List<ChoreBean> getChores(String householdName) throws SQLException {
        List<ChoreBean> choresFromHousehold = new ArrayList<ChoreBean>();
        DBConn = DriverManager.getConnection("jdbc:derby://localhost:1527/ChoreBoard");
        Statement stmt = DBConn.createStatement();
        //todo: Chore Stuff
        return choresFromHousehold;
    }
    
    public static void deleteChores(String householdName) throws SQLException
    {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("delete from chore where housename='"+ householdName+"'");
    }

    public static void createHousehold(HouseholdBean bean) throws SQLException {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("insert into household values ('" + bean.getHouseholdName() + "','" + bean.getPassphrase() + "')");
    }
    
    public static void deleteHouse(UserBean bean) throws SQLException {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("update person set household=null where household='"+bean.getHouseName()+"'");
        stmt.executeUpdate("delete from household where housename='"+bean.getHouseName()+"'");
    }
    
}